package com.ccjq.jq173.product.dao.impl;

import com.ccjq.jq173.core.util.DbConnection;
import com.ccjq.jq173.product.dao.ProductDao;
import com.ccjq.jq173.product.po.Category;
import com.ccjq.jq173.product.po.Product;
import com.ccjq.jq173.product.po.Slider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProDuctDaoImpl implements ProductDao {
    @Override
    public int getCount() {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        int k=0;

        conn= DbConnection.getConnection();

        try {
            st=conn.prepareStatement("select count(*) from t_product");
            rs=st.executeQuery();
            if(rs.next())
            {
                k=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally
        {
            DbConnection.close(rs,st,conn);
        }
        return k;
    }

    @Override
    public int getCount(String categoryId) {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        int k=0;

        conn= DbConnection.getConnection();

        try {
            st=conn.prepareStatement("select count(*) from t_product where category_id=?");
            st.setString(1,categoryId);
            rs=st.executeQuery();
            if(rs.next())
            {
                k=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally
        {
            DbConnection.close(rs,st,conn);
        }
        return k;
    }

    @Override
    public List<Product> queryAll(int startRow, int endRow) {
        String sql="select * from (select * from (select a.product_Id,description,price,model,feature,imgpath,novalty_Status,publish_Date,quantity,a.category_Id,name,rownum rn from t_product a join T_STOCK b on a.product_id=b.product_id join T_CATEGORY c on a.category_id=c.category_id) t1 where rn>=?) t2 where t2.rn<=?";

        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        ArrayList<Product> a=new ArrayList<Product>();

        conn=DbConnection.getConnection();
        try {
            st=conn.prepareStatement(sql);
            st.setInt(1,startRow);
            st.setInt(2,endRow);
            rs=st.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setProductId(rs.getString(1));
                p.setDescription(rs.getString(2));
                p.setPrice(rs.getString(3));
                p.setModel(rs.getString(4));
                p.setFeature(rs.getString(5));
                p.setImgpath(rs.getString(6));
                p.setNovaltyStatus(rs.getInt(7));
                p.setPublishDate(rs.getDate(8));
                p.setQuantity(rs.getInt(9));
                p.setCategoryId(rs.getString(10));
                p.setCategoryName(rs.getString(11));
                a.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally
        {
            DbConnection.close(rs,st,conn);
        }
        return a;
    }

    @Override
    public List<Product> queryAll(String categoryId, int startRow, int endRow) {
         String sql="select * from (select * from (select a.product_Id,description,price,model,feature,imgpath,novalty_Status,publish_Date,quantity,a.category_Id,name,rownum rn from t_product a join T_STOCK b on a.product_id=b.product_id join T_CATEGORY c on a.category_id=c.category_id where a.category_id=?) t1 where rn>=?) t2 where t2.rn<=?";
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        ArrayList<Product> a=new ArrayList<Product>();

        conn=DbConnection.getConnection();
        try {
            st=conn.prepareStatement(sql);
            st.setString(1,categoryId);
            st.setInt(2,startRow);
            st.setInt(3,endRow);
            rs=st.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setProductId(rs.getString(1));
                p.setDescription(rs.getString(2));
                p.setPrice(rs.getString(3));
                p.setModel(rs.getString(4));
                p.setFeature(rs.getString(5));
                p.setImgpath(rs.getString(6));
                p.setNovaltyStatus(rs.getInt(7));
                p.setPublishDate(rs.getDate(8));
                p.setQuantity(rs.getInt(9));
                p.setCategoryId(rs.getString(10));
                p.setCategoryName(rs.getString(11));
                a.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally
        {
            DbConnection.close(rs,st,conn);
        }
        return a;
    }

    @Override
    public Product findProductById(String productId) {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        Product p=null;


        String sql="select a.product_id,description,price,model,feature,imgpath,novalty_status,publish_date,c.quantity,a.category_id,b.name from t_product a join t_category b on a.category_id=b.category_id  join t_stock c on a.product_id=c.product_id where a.product_id=?";
        try {
            conn=DbConnection.getConnection();
            st=conn.prepareStatement(sql);
            st.setString(1,productId);
            rs=st.executeQuery();
            while(rs.next())
            {
                p=new Product();
                p.setProductId(rs.getString(1));//商品ID
                p.setDescription(rs.getString(2));//描述
                p.setPrice(rs.getString(3));//单价
                p.setModel(rs.getString(4));//型号
                p.setFeature(rs.getString(5));//特性描述
                p.setImgpath(rs.getString(6));//图片路径
                p.setNovaltyStatus(rs.getInt(7));//商品的状态  0正卖  1新品
                p.setPublishDate(rs.getDate(8));//发布时间
                p.setQuantity(rs.getInt(9));//库存资源
                p.setCategoryId(rs.getString(10));//品牌ID
                p.setCategoryName(rs.getString(11));//商品名称
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(rs,st,conn);
        }
        return p;
    }

    @Override
    public List<Product> getNominateProduct() {
        return queryHotSale();
    }

    @Override
    public List<Category> queryAll4Category() {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;

        String sql="select category_id,name from t_category";
        ArrayList<Category> a=new ArrayList<Category>();
        try {
            conn=DbConnection.getConnection();
            st=conn.prepareStatement(sql);
            rs=st.executeQuery();
            while(rs.next())
            {
                Category c=new Category();
                c.setCategoryId(rs.getString(1));
                c.setName(rs.getString(2));
                a.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally
        {
            DbConnection.close(rs,st,conn);
        }
        return a;
    }

    @Override
    public List<Product> queryHotSale() {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        ArrayList<Product> a=new ArrayList<Product>();

        String sql="select a.product_id,description,price,model,feature,imgpath,novalty_status,publish_date,c.quantity,a.category_id,b.name from t_product a join t_category b on a.category_id=b.category_id  join t_stock c on a.product_id=c.product_id where a.product_id in\n" +
                "(select product_id from (select product_id,quantity,rownum from t_order_product order by quantity desc )where rownum<=3)";
        try {
            conn=DbConnection.getConnection();
            st=conn.prepareStatement(sql);
            rs=st.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setProductId(rs.getString(1));//商品ID
                p.setDescription(rs.getString(2));//描述
                p.setPrice(rs.getString(3));//单价
                p.setModel(rs.getString(4));//型号
                p.setFeature(rs.getString(5));//特性描述
                p.setImgpath(rs.getString(6));//图片路径
                p.setNovaltyStatus(rs.getInt(7));//商品的状态  0正卖  1新品
                p.setPublishDate(rs.getDate(8));//发布时间
                p.setQuantity(rs.getInt(9));//库存资源
                p.setCategoryId(rs.getString(10));//品牌ID
                p.setCategoryName(rs.getString(11));//商品名称
                a.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return a;
    }

    @Override
    public List<Product> ququeryNeqwProduct() {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;

        String sql= "select a.product_id,description,price,model,feature,imgpath,novalty_status,publish_date,c.quantity,a.category_id,b.name from t_product a join t_category b on a.category_id=b.category_id  join t_stock c on a.product_id=c.product_id where novalty_status=?";
        ArrayList<Product> a=new ArrayList<Product>();
        try {
            conn=DbConnection.getConnection();
            st=conn.prepareStatement(sql);
            st.setInt(1,1);
            rs=st.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setProductId(rs.getString(1));//商品ID
                p.setDescription(rs.getString(2));//描述
                p.setPrice(rs.getString(3));//单价
                p.setModel(rs.getString(4));//型号
                p.setFeature(rs.getString(5));//特性描述
                p.setImgpath(rs.getString(6));//图片路径
                p.setNovaltyStatus(rs.getInt(7));//商品的状态  0正卖  1新品
                p.setPublishDate(rs.getDate(8));//发布时间
                p.setQuantity(rs.getInt(9));//库存资源
                p.setCategoryId(rs.getString(10));//品牌ID
                p.setCategoryName(rs.getString(11));//商品名称
                a.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(rs,st,conn);
        }
        return a;
    }

    @Override
    public List<Slider> querySliders() {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;

        String sql="select product_id,description,imgpath,availability from t_slider where availability=?";
        ArrayList<Slider> a=new ArrayList<Slider>();
        try {
            conn=DbConnection.getConnection();
            conn.prepareStatement(sql);
            st=conn.prepareStatement(sql);
            st.setInt(1,1);
            rs=st.executeQuery();
            while(rs.next())
            {
                Slider s=new Slider();
                s.setProduct_id(rs.getString(1));
                s.setDescription(rs.getString(2));
                s.setImgpath(rs.getString(3));
                s.setAvailability(rs.getInt(4));
                a.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(rs,st,conn);
        }
        return a;
    }

    @Override
    public Category queryCategoryById(String categoryId) {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;

        String sql="select category_id,name from t_category where category_id=?";
        Category c=null;
        try {
            conn=DbConnection.getConnection();
            st=conn.prepareStatement(sql);
            st.setString(1,categoryId);
            rs=st.executeQuery();
            if(rs.next())
            {
                c=new Category();
                c.setCategoryId(rs.getString(1));
                c.setName(rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(rs,st,conn);
        }
        return c;
    }

    @Override
    public int getStockByProductId(String productId) {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;

        int quantity=0;
        String sql="select quantity from t_stock where  product_id=?";
        try {
            conn=DbConnection.getConnection();
            st=conn.prepareStatement(sql);
            st.setString(1,productId);
            rs=st.executeQuery();
            if(rs.next())
            {
                quantity=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(rs,st,conn);
        }
        System.out.println("dao_quantity"+quantity);
        return quantity;
    }
}
